﻿using System;
using System.Data;

namespace org.se.fs.model.Table
{
    public class Client : DataContext
    {
        public DataTable GetClientLists(UInt16 page, UInt16 size, string filterString)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(3);
                databaseManager.AddParameters(0, "page", page);
                databaseManager.AddParameters(1, "size", size);
                databaseManager.AddParameters(2, "filterString", filterString);
                var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetClientLists");
                return ToDataTable(dataSet);
            }
        }

        public UInt64 Insert(string lastName, string firstName, string middleName, string gender, string address, DateTime birthDate, string email, string mobileNum, string telNum, UInt64 userCreateId)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(10);
                databaseManager.AddParameters(0, "last_name", lastName);
                databaseManager.AddParameters(1, "first_name", firstName);
                databaseManager.AddParameters(2, "middle_name", middleName);
                databaseManager.AddParameters(3, "gender", gender);
                databaseManager.AddParameters(4, "address", address);
                databaseManager.AddParameters(5, "birth_date", birthDate);
                databaseManager.AddParameters(6, "email", email);
                databaseManager.AddParameters(7, "mobile_num",mobileNum );
                databaseManager.AddParameters(8, "tel_num",telNum );
                databaseManager.AddParameters(9, "user_create_id", userCreateId);
                var result = databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertClient");
                return Convert.ToUInt64(result);
            }
        }

        public bool Update(UInt64 id, string lastName, string firstName, string middleName, string gender, string address, DateTime birthDate, string email, string mobileNum, string telNum, UInt64 userCreateId)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(11);
                databaseManager.AddParameters(0, "id", id);
                databaseManager.AddParameters(1, "last_name", lastName);
                databaseManager.AddParameters(2, "first_name", firstName);
                databaseManager.AddParameters(3, "middle_name", middleName);
                databaseManager.AddParameters(4, "gender", gender);
                databaseManager.AddParameters(5, "address", address);
                databaseManager.AddParameters(6, "birth_date", birthDate);
                databaseManager.AddParameters(7, "email", email);
                databaseManager.AddParameters(8, "mobile_num", mobileNum);
                databaseManager.AddParameters(9, "tel_num", telNum);
                databaseManager.AddParameters(10, "user_create_id", userCreateId);
                var result = databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateClient");
                return Convert.ToBoolean(result);
            }
        }

        public bool Delete(UInt64 uid)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(1);
                databaseManager.AddParameters(0, "id", uid);
                var result = databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "DeleteClient");
                return Convert.ToBoolean(result);
            }
        }

        public UInt16 GetCount()
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(1);
                databaseManager.AddParameters(0, "tableName", "client");
                var result = databaseManager.ExecuteScalar(CommandType.StoredProcedure, "GetCountTable");
                return Convert.ToUInt16(result);
            }
        }
    }
}
